package org.lib.postgresqlhello;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;
import java.util.concurrent.TimeUnit;

import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.annotation.MapperScans;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

import lombok.Data;

@SpringBootApplication
// 单数据源时使用，，多数据源时，扫描注解放到另外的位置
// 扫描*Mapper
@MapperScans({
	@MapperScan(value="org.lib.postgresqlhello.user"),
	@MapperScan(value="org.lib.postgresqlhello.device"),
	@MapperScan(value="org.lib.postgresqlhello.record"),
})
// baomidou-动态多数据源时，
//加exclude 效果同 spring.autoconfigure.exclude的配置
//@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
public class PostgresqlHelloApplication {

	public static void main(String[] args) {
		ConfigurableApplicationContext ctx = SpringApplication.run(PostgresqlHelloApplication.class, args);
	
		Arrays.asList(ctx.getBeanDefinitionNames()).forEach(name->{
			String ln = name.toLowerCase();
			if (ln.contains("sql") || ln.contains("session") || ln.contains("datasource")) {
				System.out.println("sql bean=" + name);
			}
		});
	}

}

/**
 * 手动连接数据库
 * @author ben
 * @date 2021-11-03 22:54:35 CST
 */
//@Component
class DbConn implements ApplicationRunner {

	@Override
	public void run(ApplicationArguments args) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		
		// 属性
		String url = "jdbc:postgresql://localhost:5432/lib1";
		String uname = "postgres";
		String pwd = "123456";
		
		Class.forName("org.postgresql.Driver");
		
		conn = DriverManager.getConnection(url, uname, pwd);
		System.out.println("数据库连接成功！conn=" + conn);
		
		// 试验 CRUD
		
		stmt = conn.createStatement();
		System.out.println("stmt=" + stmt);
		
		// 成功：新增语句，返回false
		boolean ret1 = stmt.execute("INSERT INTO tab1(name,age) VALUES ('s.boot." + System.currentTimeMillis() + "', 20);");
		System.out.println("ret1=" + ret1);
		
		// 停顿下，去查询数据库
		TimeUnit.SECONDS.sleep(5);
		
		ResultSet rs = stmt.executeQuery("SELECT id,name,age FROM tab1;");
		System.out.println("rs=" + rs);
		while(rs.next()) {
			Tab1 t = new Tab1();
			// 从1开始
			t.setId(rs.getLong(1));
			t.setName(rs.getString(2));
			t.setAge(rs.getInt(3));
			System.out.println("record=" + t);
		}
		rs.close();
		System.out.println("rs closed=" + rs);
		
		TimeUnit.SECONDS.sleep(5);
		
		// 修改
		// 语法：UPDATE [ ONLY ] 表名 [ * ] [ [ AS ] 别名 ] ... 
//		int ret3 = stmt.executeUpdate("UPDATE TABLE tab1 SET name = 's.boot.modified' WHERE id = 3;");
		int ret3 = stmt.executeUpdate("UPDATE tab1 SET name = 's.boot.modified' WHERE id = 3;");
		System.out.println("ret3=" + ret3);

		TimeUnit.SECONDS.sleep(5);
		
		// 删除
		int ret4 = stmt.executeUpdate("DELETE FROM tab1 WHERE id = 3;");
		System.out.println("ret4=" + ret4);
		
		TimeUnit.SECONDS.sleep(5);
		
		stmt.close();
		System.out.println("stmt closed=" + rs);
		
		conn.close();
		System.out.println("关闭数据库连接！conn=" + conn);
	}
	
	/**
	 * 对应数据表 tab1
	 * @author ben
	 * @date 2021-11-03 23:21:12 CST
	 */
	@Data
	private static class Tab1 {
		
		private Long id;
		
		private String name;
		
		private Integer age;
		
	}
	
}
